﻿using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace ST.Common.Util.Office
{
	public static class NPOIExtension
	{
		public static IFont CreateFont(this IWorkbook wb, string fontName, double fontSize)
		{
			var font = wb.CreateFont();
			font.FontName = fontName;
			font.FontHeightInPoints = fontSize;
			return font;
		}

		/// <summary>
		/// 创建垂直居中、有边框的单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb)
		{
			ICellStyle style = wb.CreateCellStyle();
			style.WrapText = true;//列自动换行
			style.VerticalAlignment = VerticalAlignment.Center;
			style.BorderTop = BorderStyle.Thin;
			style.BorderBottom = BorderStyle.Thin;
			style.BorderLeft = BorderStyle.Thin;
			style.BorderRight = BorderStyle.Thin;
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, HorizontalAlignment hAlign)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb);
			style.Alignment = hAlign;
			return style;
		}

		/// <summary>
		/// 创建垂直居中、有边框的单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, short bgColor)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb);
			style.FillPattern = FillPattern.SolidForeground;
			style.FillForegroundColor = bgColor;
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor);
			style.VerticalAlignment = vAlign;
			style.Alignment = hAlign;
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign, IFont font)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, hAlign, vAlign);
			style.SetFont(font);
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, string formatStr)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor);
			style.Alignment = hAlign;
			IDataFormat format = wb.CreateDataFormat();
			style.DataFormat = format.GetFormat(formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleWithBorder(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign, string formatStr)
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, hAlign, formatStr);
			style.VerticalAlignment = vAlign;
			return style;
		}

		/// <summary>
		/// 创建默认右对齐的金额单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfMoney(this IWorkbook wb, short bgColor, string formatStr = "¥#,##0.00")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, HorizontalAlignment.Right, formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式
		/// </summary>
		/// <param name="wb"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfMoney(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign, string formatStr = "¥#,##0.00")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, hAlign, vAlign, formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式:逗号分隔的形式，保留两位小数
		/// </summary>
		/// <param name="wb"></param>
		/// <param name="bgColor"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <param name="formatStr"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfNumber(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign, string formatStr = "#,##0.00")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, hAlign, vAlign, formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式:逗号分隔的形式，保留两位小数
		/// </summary>
		/// <param name="wb"></param>
		/// <param name="bgColor"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <param name="formatStr"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfNumber(this IWorkbook wb, string formatStr = "#,##0.00")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb);
			style.Alignment = HorizontalAlignment.Right;
			style.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式:逗号分隔的形式，保留两位小数
		/// </summary>
		/// <param name="wb"></param>
		/// <param name="bgColor"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <param name="formatStr"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfInteger(this IWorkbook wb, short bgColor, HorizontalAlignment hAlign, VerticalAlignment vAlign, string formatStr = "#,##0")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb, bgColor, hAlign, vAlign, formatStr);
			return style;
		}

		/// <summary>
		/// 创建单元格样式:逗号分隔的形式，保留两位小数
		/// </summary>
		/// <param name="wb"></param>
		/// <param name="bgColor"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <param name="formatStr"></param>
		/// <returns></returns>
		public static ICellStyle CreateCellStyleOfInteger(this IWorkbook wb, string formatStr = "#,##0")
		{
			ICellStyle style = CreateCellStyleWithBorder(wb);
			style.Alignment = HorizontalAlignment.Right;
			style.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
			return style;
		}

		public static void MergeCell(this ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
		{
			CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
			sheet.AddMergedRegion(cra);
		}

		public static void MergeCell(this ISheet sheet, int rowIndex, int colIndex, int colSpan)
		{
			CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + colSpan);
			sheet.AddMergedRegion(cra);
		}

		public static void MergeCell(this ISheet sheet, IRow row, int colIndex, int colSpan)
		{
			CellRangeAddress cra = new CellRangeAddress(row.RowNum, row.RowNum, colIndex, colIndex + colSpan);
			sheet.AddMergedRegion(cra);
		}

		public static ICell CreateCell(this IRow row, int colIndex)
		{
			return row.CreateCell(colIndex);
		}

		public static void CreateCell(this IRow row, int colIndex, string value)
		{
			row.CreateCell(colIndex).SetCellValue(value);
		}

		public static void CreateCell(this IRow row, int colIndex, double value)
		{
			row.CreateCell(colIndex).SetCellValue(value);
		}

		public static void CreateCell(this IRow row, int colIndex, string value, ICellStyle cellStyle)
		{
			ICell cell = row.CreateCell(colIndex);
			cell.SetCellValue(value);
			cell.CellStyle = cellStyle;
		}

		public static void CreateCell(this IRow row, int colIndex, double value, ICellStyle cellStyle)
		{
			ICell cell = row.CreateCell(colIndex);
			cell.SetCellValue(value);
			cell.CellStyle = cellStyle;
		}

		//public static void MergeRow(this ISheet sheet, int rowIndex, int rowSpan)
		//{
		//	sheet.LastRowNum
		//}

		//public static void MergeCell(this ISheet sheet, int rowIndex, int rowSpan)
		//{

		//}
	}
}
